package com.hnqcgczy.sgzshop.dao.impl;

import com.hnqcgczy.sgzshop.dao.IProductDao;
import com.hnqcgczy.sgzshop.dbc.MyTransaction;
import com.hnqcgczy.sgzshop.vo.TProduct;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements IProductDao {
    private MyTransaction myTransaction;

    public ProductDaoImpl(MyTransaction myTransaction) {
        this.myTransaction = myTransaction;
    }


    @Override
    public List<TProduct> listAll() throws SQLException {
        Connection conn = myTransaction.getConn();
        String sqlsql="select * from t_product";
        PreparedStatement ps = conn.prepareStatement(sqlsql);
        ResultSet rs = ps.executeQuery();
        List<TProduct> list=new ArrayList<>();
        while(rs.next()){
            TProduct  tProduct =new TProduct();
            tProduct.setTpId(rs.getInt(1));
            tProduct.setTpName(rs.getString(2));
            tProduct.setTpDescription(rs.getString(3));
            tProduct.setTpPrice(rs.getDouble(4));
            tProduct.setTpStock(rs.getInt(5));
            tProduct.setTpcId(rs.getInt(6));
            tProduct.setTpcChildId(rs.getInt(7));
            tProduct.setTpFileName(rs.getString(8));

            list.add(tProduct);//每循环一次就放一次
        }
        //释放资源
        rs.close();
        ps.close();
        //将数据库连接放回连接池
        myTransaction.closeConn();
        return list;
    }

    /**
     *
     * @param tProduct
     * @param page 显示第几页
     * @param pageSize  每页显示记录数
     * @return
     * @throws SQLException
     */
    @Override
    public List<TProduct> listAny(TProduct tProduct, int page, int pageSize) throws SQLException {
        Connection conn = myTransaction.getConn();
        StringBuffer sql=new StringBuffer("select * from t_product where 1=1");
        if(!tProduct.getTpName().equals("")){
            sql.append(" and tp_name like ?");
        }
        if(!tProduct.getTpDescription().equals("")){
            sql.append(" and tp_description like ?");
        }
        sql.append(" limit ?,?");
        PreparedStatement ps = conn.prepareStatement(sql.toString());
        ps.setString(1,"%"+tProduct.getTpName()+"%");
        ps.setString(2,"%"+tProduct.getTpDescription()+"%");
        ps.setInt(3,(page-1)*pageSize);
        ps.setInt(4,pageSize);

        List<TProduct> list=new ArrayList<>();
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            TProduct  p1 =new TProduct();
            p1.setTpId(rs.getInt(1));
            p1.setTpName(rs.getString(2));
            p1.setTpDescription(rs.getString(3));
            p1.setTpPrice(rs.getDouble(4));
            p1.setTpStock(rs.getInt(5));
            p1.setTpcId(rs.getInt(6));
            p1.setTpcChildId(rs.getInt(7));
            p1.setTpFileName(rs.getString(8));

            list.add(p1);//每循环一次就放一次
        }
        //释放资源
        rs.close();
        ps.close();
        //将数据库连接放回连接池
        myTransaction.closeConn();
        return list;
    }

    @Override
    public TProduct getProductById(int tp_id) throws SQLException {
        //1、获得数据库连接
        Connection conn = myTransaction.getConn();
        //2、向数据库发送sql语句 ，由PreparedStatement发送
        String sqlsql="select * from t_product where tp_id=?";
        PreparedStatement ps = conn.prepareStatement(sqlsql);
        ps.setInt(1,tp_id);
        //3、执行sql语句，得到数据放虚拟表
        ResultSet rs = ps.executeQuery();
        TProduct tProduct=null;
        //4、将虚拟表ResultSet中的数据放内存中来
        if(rs.next()){
            tProduct =new TProduct();
            tProduct.setTpId(rs.getInt(1));
            tProduct.setTpName(rs.getString(2));
            tProduct.setTpDescription(rs.getString(3));
            tProduct.setTpPrice(rs.getDouble(4));
            tProduct.setTpStock(rs.getInt(5));
            tProduct.setTpcId(rs.getInt(6));
            tProduct.setTpcChildId(rs.getInt(7));
            tProduct.setTpFileName(rs.getString(8));
        }
        //5、释放资源
        rs.close();
        ps.close();
        //6、将数据库连接放回连接池
        myTransaction.closeConn();
        return tProduct;
    }
}
